/*
 * This class is part of the book "iText in Action - 2nd Edition"
 * written by Bruno Lowagie (ISBN: 9781935182610)
 * For more info, go to: http://itextpdf.com/examples/
 * This example only works with the AGPL version of iText.
 */

package com.lowagie.filmfestival;

import java.io.UnsupportedEncodingException;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.lowagie.database.DatabaseConnection;

/**
 * A factory that makes it easy to query the database using a series of static methods.
 */
public class PojoFactory {
	/** SQL statement to get all the movies of the festival. */
	public static final String MOVIES = "SELECT m.id, m.title, m.original_title, m.imdb, m.year, m.duration, "
			+ "e.year, c.name, c.keyword, c.color " + "FROM film_movietitle m, festival_entry e, festival_category c "
			+ "WHERE m.id = e.film_id AND e.category_id = c.id " + "ORDER BY m.title";
	/** SQL statement to get the directors of a specific movie. */
	public static final String DIRECTORS = "SELECT d.name, d.given_name "
			+ "FROM film_director d, film_movie_director md " + "WHERE md.film_id = ? AND md.director_id = d.id";
	/** SQL statement to get the movies of a specific director. */
	public static final String MOVIEDIRECTORS = "SELECT m.id, m.title, m.original_title, m.imdb, m.year, m.duration "
			+ "FROM film_movietitle m, film_movie_director md " + "WHERE md.director_id = ? AND md.film_id = m.id "
			+ "ORDER BY m.title";
	/** SQL statement to get the countries of a specific movie. */
	public static final String COUNTRIES = "SELECT c.country " + "FROM film_country c, film_movie_country mc "
			+ "WHERE mc.film_id = ? AND mc.country_id = c.id";
	/** SQL statement to get the movies from a specific country. */
	public static final String MOVIECOUNTRIES = "SELECT m.id, m.title, m.original_title, m.imdb, m.year, m.duration "
			+ "FROM film_movietitle m, film_movie_country mc " + "WHERE mc.country_id = ? AND mc.film_id = m.id "
			+ "ORDER BY m.title";
	/** SQL statement to get all the days of the festival. */
	public static final String DAYS = "SELECT DISTINCT day FROM festival_screening ORDER BY day";
	/** SQL statament to get all the locations at the festival */
	public static final String LOCATIONS = "SELECT DISTINCT location FROM festival_screening ORDER by location";
	/** SQL statement to get screenings. */
	public static final String SCREENINGS = "SELECT m.title, m.original_title, m.imdb, m.year, m.duration,"
			+ "s.day, s.time, s.location, s.press, " + "e.year, c.name, c.keyword, c.color, m.id "
			+ "FROM festival_screening s, film_movietitle m, " + "festival_entry e, festival_category c "
			+ "WHERE day = ? AND s.film_id = m.id " + "AND m.id = e.film_id AND e.category_id = c.id";
	/** SQL statement to get screenings. */
	public static final String MOVIESCREENINGS = "SELECT s.day, s.time, s.location, s.press "
			+ "FROM festival_screening s " + "WHERE s.film_id = ?";
	/** SQL statement to get screenings. */
	public static final String PRESS = "SELECT m.title, m.original_title, m.imdb, m.year, m.duration,"
			+ "s.day, s.time, s.location, s.press, " + "e.year, c.name, c.keyword, c.color, m.id "
			+ "FROM festival_screening s, film_movietitle m, " + "festival_entry e, festival_category c "
			+ "WHERE s.press=1 AND s.film_id = m.id " + "AND m.id = e.film_id AND e.category_id = c.id "
			+ "ORDER BY day, time ASC";

	/**
	 * Fills a Movie POJO using a ResultSet.
	 * 
	 * @param rs
	 *          a ResultSet with records from table film_movietitle
	 * @return a Movie POJO
	 * @throws UnsupportedEncodingException
	 */
	public static Movie getMovie(ResultSet rs) throws SQLException, UnsupportedEncodingException {
		Movie movie = new Movie();
		movie.setTitle(new String(rs.getBytes("title"), "UTF-8"));
		if (rs.getObject("original_title") != null)
			movie.setOriginalTitle(new String(rs.getBytes("original_title"), "UTF-8"));
		movie.setImdb(rs.getString("imdb"));
		movie.setYear(rs.getInt("year"));
		movie.setDuration(rs.getInt("duration"));
		return movie;
	}

	/**
	 * Fills a Director POJO using a ResultSet.
	 * 
	 * @param rs
	 *          a ResultSet with records from table file_director
	 * @return a Director POJO
	 */
	public static Director getDirector(ResultSet rs) throws SQLException, UnsupportedEncodingException {
		Director director = new Director();
		director.setName(new String(rs.getBytes("name"), "UTF-8"));
		director.setGivenName(new String(rs.getBytes("given_name"), "UTF-8"));
		return director;
	}

	/**
	 * Fills a Country POJO using a ResultSet.
	 * 
	 * @param rs
	 *          a ResultSet with records from table file_director
	 * @return a Director POJO
	 */
	public static Country getCountry(ResultSet rs) throws SQLException {
		Country country = new Country();
		country.setCountry(rs.getString("country"));
		return country;
	}

	/**
	 * Fills an Entry POJO using a ResultSet.
	 * 
	 * @param rs
	 *          a resultSet with records from table festival_entry
	 * @return an Entry POJO
	 */
	public static Entry getEntry(ResultSet rs) throws SQLException {
		Entry entry = new Entry();
		entry.setYear(rs.getInt("year"));
		return entry;
	}

	/**
	 * Fills a Category POJO using a ResultSet.
	 * 
	 * @param rs
	 *          a resultSet with records from table festival_category
	 * @return a Category POJO
	 */
	public static Category getCategory(ResultSet rs) throws SQLException {
		Category category = new Category();
		category.setName(rs.getString("name"));
		category.setKeyword(rs.getString("keyword"));
		category.setColor(rs.getString("color"));
		return category;
	}

	/**
	 * Fills a Screening POJO using a ResultSet.
	 * 
	 * @param rs
	 *          a ResultSet with records from table festival_screening
	 * @return a Screening POJO
	 */
	public static Screening getScreening(ResultSet rs) throws SQLException {
		Screening screening = new Screening();
		screening.setDate(rs.getDate("day"));
		screening.setTime(rs.getTime("time"));
		screening.setLocation(rs.getString("location"));
		screening.setPress(rs.getInt("press") == 1);
		return screening;
	}

	/**
	 * Returns a list with Screening objects, if you pass a DatabaseConnection and a day.
	 * 
	 * @param connection
	 *          a connection to the film festival database
	 * @param day
	 *          a day (java.sql.Date)
	 * @return a List of Screening POJOs
	 * @throws UnsupportedEncodingException
	 */
	public static List<Screening> getScreenings(DatabaseConnection connection, Date day) throws SQLException,
			UnsupportedEncodingException {
		List<Screening> list = new ArrayList<Screening>();
		PreparedStatement stm = connection.createPreparedStatement(SCREENINGS);
		stm.setDate(1, day);
		ResultSet rs = stm.executeQuery();
		Screening screening;
		Movie movie;
		Entry entry;
		Category category;
		while (rs.next()) {
			screening = getScreening(rs);
			movie = getMovie(rs);
			for (Director director : getDirectors(connection, rs.getInt("id"))) {
				movie.addDirector(director);
			}
			for (Country country : getCountries(connection, rs.getInt("id"))) {
				movie.addCountry(country);
			}
			entry = getEntry(rs);
			category = getCategory(rs);
			entry.setCategory(category);
			entry.setMovie(movie);
			movie.setEntry(entry);
			screening.setMovie(movie);
			list.add(screening);
		}
		stm.close();
		return list;
	}

	/**
	 * Returns a list with Screening objects, if you pass a DatabaseConnection and a day.
	 * 
	 * @param connection
	 *          a connection to the film festival database
	 * @param film_id
	 *          a movie id
	 * @return a List of Screening POJOs
	 * @throws UnsupportedEncodingException
	 */
	public static List<Screening> getScreenings(DatabaseConnection connection, int film_id) throws SQLException,
			UnsupportedEncodingException {
		List<Screening> list = new ArrayList<Screening>();
		PreparedStatement stm = connection.createPreparedStatement(MOVIESCREENINGS);
		stm.setInt(1, film_id);
		ResultSet rs = stm.executeQuery();
		Screening screening;
		while (rs.next()) {
			screening = getScreening(rs);
			list.add(screening);
		}
		stm.close();
		return list;
	}

	/**
	 * Returns a list with Screening objects, if you pass a DatabaseConnection and a day.
	 * 
	 * @param connection
	 *          a connection to the film festival database
	 * @param day
	 *          a day (java.sql.Date)
	 * @return a List of Screening POJOs
	 * @throws UnsupportedEncodingException
	 */
	public static List<Screening> getPressPreviews(DatabaseConnection connection) throws SQLException,
			UnsupportedEncodingException {
		List<Screening> list = new ArrayList<Screening>();
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery(PRESS);
		Screening screening;
		Movie movie;
		Entry entry;
		Category category;
		while (rs.next()) {
			screening = getScreening(rs);
			movie = getMovie(rs);
			for (Director director : getDirectors(connection, rs.getInt("id"))) {
				movie.addDirector(director);
			}
			for (Country country : getCountries(connection, rs.getInt("id"))) {
				movie.addCountry(country);
			}
			entry = getEntry(rs);
			category = getCategory(rs);
			entry.setCategory(category);
			entry.setMovie(movie);
			movie.setEntry(entry);
			screening.setMovie(movie);
			list.add(screening);
		}
		stm.close();
		return list;
	}

	/**
	 * Returns a list with Movie objects.
	 * 
	 * @param connection
	 *          a connection to the filmfestival database
	 * @return a List of Screening POJOs
	 * @throws SQLException
	 * @throws UnsupportedEncodingException
	 */
	public static List<Movie> getMovies(DatabaseConnection connection) throws SQLException, UnsupportedEncodingException {
		List<Movie> list = new ArrayList<Movie>();
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery(MOVIES);
		Movie movie;
		Entry entry;
		Category category;
		while (rs.next()) {
			movie = getMovie(rs);
			entry = getEntry(rs);
			category = getCategory(rs);
			entry.setCategory(category);
			for (Screening screening : getScreenings(connection, rs.getInt("id"))) {
				entry.addScreening(screening);
			}
			movie.setEntry(entry);
			for (Director director : getDirectors(connection, rs.getInt("id"))) {
				movie.addDirector(director);
			}
			for (Country country : getCountries(connection, rs.getInt("id"))) {
				movie.addCountry(country);
			}
			list.add(movie);
		}
		return list;
	}

	/**
	 * Returns a list with Director objects.
	 * 
	 * @param connection
	 *          a connection to the filmfestival database
	 * @param director_id
	 *          the id of a movie
	 * @return a List of Screening POJOs
	 * @throws SQLException
	 * @throws UnsupportedEncodingException
	 */
	public static List<Director> getDirectors(DatabaseConnection connection, int movie_id) throws SQLException,
			UnsupportedEncodingException {
		List<Director> list = new ArrayList<Director>();
		PreparedStatement directors = connection.createPreparedStatement(DIRECTORS);
		directors.setInt(1, movie_id);
		ResultSet rs = directors.executeQuery();
		while (rs.next()) {
			list.add(getDirector(rs));
		}
		return list;
	}

	/**
	 * Returns a list with Country objects.
	 * 
	 * @param connection
	 *          a connection to the filmfestival database
	 * @param movie_id
	 *          the id of a movie
	 * @return a List of Screening POJOs
	 * @throws SQLException
	 * @throws UnsupportedEncodingException
	 */
	public static List<Country> getCountries(DatabaseConnection connection, int movie_id) throws SQLException,
			UnsupportedEncodingException {
		List<Country> list = new ArrayList<Country>();
		PreparedStatement countries = connection.createPreparedStatement(COUNTRIES);
		countries.setInt(1, movie_id);
		ResultSet rs = countries.executeQuery();
		while (rs.next()) {
			list.add(getCountry(rs));
		}
		return list;
	}

	/**
	 * Returns a list with Movie objects.
	 * 
	 * @param connection
	 *          a connection to the filmfestival database
	 * @param director_id
	 *          the id of a director
	 * @return a List of Screening POJOs
	 * @throws SQLException
	 * @throws UnsupportedEncodingException
	 */
	public static List<Movie> getMovies(DatabaseConnection connection, int director_id) throws SQLException,
			UnsupportedEncodingException {
		List<Movie> list = new ArrayList<Movie>();
		PreparedStatement movies = connection.createPreparedStatement(MOVIEDIRECTORS);
		movies.setInt(1, director_id);
		ResultSet rs = movies.executeQuery();
		Movie movie;
		while (rs.next()) {
			movie = getMovie(rs);
			for (Country country : getCountries(connection, rs.getInt("id"))) {
				movie.addCountry(country);
			}
			list.add(movie);
		}
		return list;
	}

	/**
	 * Returns a list with Movie objects.
	 * 
	 * @param connection
	 *          a connection to the filmfestival database
	 * @param country_id
	 *          the id of a country
	 * @return a List of Screening POJOs
	 * @throws SQLException
	 * @throws UnsupportedEncodingException
	 */
	public static List<Movie> getMovies(DatabaseConnection connection, String country_id) throws SQLException,
			UnsupportedEncodingException {
		List<Movie> list = new ArrayList<Movie>();
		PreparedStatement movies = connection.createPreparedStatement(MOVIECOUNTRIES);
		movies.setString(1, country_id);
		ResultSet rs = movies.executeQuery();
		while (rs.next()) {
			Movie movie = getMovie(rs);
			for (Director director : getDirectors(connection, rs.getInt("id"))) {
				movie.addDirector(director);
			}
			list.add(movie);
		}
		return list;
	}

	/**
	 * Returns an ArrayList containing all the filmfestival days.
	 * 
	 * @param connection
	 *          a connection to the database.
	 * @return a list containing dates.
	 */
	public static List<Date> getDays(DatabaseConnection connection) throws SQLException {
		List<Date> list = new ArrayList<Date>();
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery(DAYS);
		while (rs.next()) {
			list.add(rs.getDate("day"));
		}
		stm.close();
		return list;
	}

	/**
	 * Returns an ArrayList containing all the screening locations.
	 * 
	 * @param connection
	 *          a connection to the database.
	 * @return a list containing location codes.
	 */
	public static List<String> getLocations(DatabaseConnection connection) throws SQLException {
		List<String> list = new ArrayList<String>();
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery(LOCATIONS);
		while (rs.next()) {
			list.add(rs.getString("location"));
		}
		stm.close();
		return list;
	}
}
